package com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.vo.ResistEarthquakesCalculationVO;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.entity.ResistEarthquakesPE;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.entity.ResistEarthquakesPECalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.param.ResistEarthquakesPECalculationParam;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.repository.ResistEarthquakesPERepository;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.vo.ResistEarthquakesPCCPEVO;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.vo.ResistEarthquakesXYPEVO;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/06/17
 * @Description: 抗震能力_概率评估Repository
 */
@Repository
public class ResistEarthquakesPERepositoryImpl implements ResistEarthquakesPERepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗震能力_正确评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_resist_earthquakes_assess_pd";
    //抗震能力_正确评估_评估记录_计算(烈度,概率)(地理id,房屋单体id) 合并为一个字段
    private static final String TABLE_CALCULATION = "fxqh_resist_earthquakes_calculation_pd";

    @Override
    public void addAssess(ResistEarthquakesPE param) {
        String sql = "insert into " + TABLE_NAME + "(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode,radio2,geom,risk) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode,:radio2,st_geomfromtext(:geom,4490),:risk)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public ResistEarthquakesPE findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResistEarthquakesPE.class), id);
    }

    @Override
    public void upDateAssess(ResistEarthquakesPE param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME + " where del_flag = '0' and assess_code like '%" + code + "%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by geography_id ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPE.class)).size();
        List<ResistEarthquakesPE> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPE.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //批量新增计算值
    @Override
    public void batchCalculation(List<ResistEarthquakesPECalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,extreme,calculated_value_p1,correction_value_p1,coordinate_range,create_user,create_time,years,extends1,damage_degree,geom,pt,area,correction_value_pt)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:extreme,:calculatedValueP1,:correctionValueP1,:coordinateRange,:createUser,:createTime,:years,:extends1,:damageDegree,ST_GeometryFromText(:geom,4490),:pt,:area,:correctionValuePt)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }
    @Override
    public void batchCalculation1(List<ResistEarthquakesPECalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,coordinate_range,create_user,create_time,years,extends1,damage_degree,pt,correction_value_pt)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:coordinateRange,:createUser,:createTime,:years,:extends1,:damageDegree,:pt,:correctionValuePt)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    //批量新增计算值
    @Override
    public void batchCalculation_new(List<ResistEarthquakesPECalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,extreme,coordinate_range,create_user,create_time,years,extends1,damage_degree,geom,pt,area,correction_value_pt)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:extreme,:coordinateRange,:createUser,:createTime,:years,:extends1,:damageDegree,ST_GeometryFromText(:geom,4490),:pt,:area,:correctionValuePt)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<ResistEarthquakesPCCPEVO> getListPCC(ResistEarthquakesPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //T年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            whereSql.append(" and years = '" + param.getYears() + "'");
        }
        //破坏状态
        if (!PlatformObjectUtils.isEmpty(param.getDamageDegree())) {
            whereSql.append(" and damage_degree = '" + param.getDamageDegree() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            whereSql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            whereSql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            whereSql.append(" and county_name = '" + param.getCounty() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql).append(" order by geography_id,damage_degree asc").toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPCCPEVO.class));
    }

    @Override
    public List<ResistEarthquakesXYPEVO> getListXY(ResistEarthquakesPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //T年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            whereSql.append(" and years = '" + param.getYears() + "'");
        }
        //破坏状态
        if (!PlatformObjectUtils.isEmpty(param.getDamageDegree())) {
            whereSql.append(" and damage_degree = '" + param.getDamageDegree() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesXYPEVO.class));
    }

    @Override
    public Map<String, Object> queryCalculation(int curPage, int pageSize, String id, String years, String damageDegree) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION + " where assess_id = '" + id + "' ");
        if (StringUtils.isNotBlank(years)) {
            sql.append(" and years ='" + years + "' ");
        }
        if (StringUtils.isNotBlank(years)) {
            sql.append(" and damage_degree ='" + damageDegree + "' ");
        }
        StringBuilder orderSql = new StringBuilder(" order by geography_id asc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPECalculation.class)).size();
        List<ResistEarthquakesPECalculation> list = jdbcTemplate.query(sql.append(orderSql).append(limitSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPECalculation.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<ResistEarthquakesPECalculation> queryCalculation1(String assessId) {
        String sql = "select * from " + TABLE_CALCULATION + " where assess_id = '" + assessId + "' and years = '1'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResistEarthquakesPECalculation.class));
    }

    @Override
    public void updateCorrectionValue(ResistEarthquakesPECalculation param) {
        String sql = "update " + TABLE_CALCULATION + " set correction_value_pt =:correctionValuePt," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }
//    public void updateCorrectionValue(ResistEarthquakesPECalculation param) {
//        String sql = "update " + TABLE_CALCULATION + " set correction_value_p1 =:correctionValueP1,pt = :pt," +
//                "update_user = :updateUser ,update_time = :updateTime where id = :id";
//        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
//        namedParameterJdbcTemplate.update(sql, source);
//    }

    @Override
    public Boolean getSTIntersects(String geomText, Double longitude, Double latitude) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490))";

        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, longitude, latitude);
    }

    @Override
    public void deleteCalculationByQuery(ResistEarthquakesPECalculation obj) {
        String sql = "delete from " + TABLE_CALCULATION + " where assess_id='" + obj.getAssessId() +
                "' and years = '" + obj.getYears() +
                "' and county_name = '" + obj.getCountyName() +
                "' and damage_degree ='" + obj.getDamageDegree() + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                                    String assessCodes, String condition) {
        String sql = "SELECT SUM(CAST(t1.correction_value_p1 AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT, t1" +
                ".damage_degree AS damageDegree , t1.area AS area, t1.extends1 extends1" +
                " FROM fxqh_resist_earthquakes_calculation_pd t1" +
                " LEFT JOIN ( SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='" + assessCodes + "') t2 ON t1.assess_id = t2.ID" +
                " WHERE t1.province_name = ? AND t1.city_name = ? AND t1.county_name = ? AND t1.years = ? AND t2.del_flag = '0'" +
                " GROUP BY t1.extends1, t1.damage_degree, t1.area ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql, province, city, county, condition);
    }

    @Override
    public List<Map<String, Object>> getSumByTaskId(String taskId) {
        String sql = "SELECT SUM(CAST(t1.correction_value_pt AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT, t1" +
                ".damage_degree AS damageDegree , t1.area AS area, t1.extends1 extends1, t1.province_name as provinceName, t1.city_name as cityName,t1.county_name as countyName, t1.years as years" +
                " FROM fxqh_resist_earthquakes_calculation_pd t1" +
                " WHERE t1.assess_id = '" + taskId + "'" +
                " GROUP BY t1.province_name, t1.city_name,t1.county_name, t1.years,t1.extends1, t1.damage_degree, t1.area ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public Integer getCountByAssessId(String assessId) {
        String sql = "SELECT count(*) FROM fxqh_resist_earthquakes_calculation_pd where assess_id='"+assessId+"'";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    @Override
    public int deleteCalculation(String taskId) {
        return jdbcTemplate.update("delete from fxqh_resist_earthquakes_calculation_pd where assess_id='"+taskId+"'");
    }
    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code) {
        String sql = "";
        if("0".equals(code)){
            sql = "select avg(correction_value_pt) val,province_name areaName from fxqh_resist_earthquakes_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"')  group by province_name order by val";
        }else{
            sql = "select avg(correction_value_pt) val,city_name as area_name from fxqh_resist_earthquakes_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') GROUP BY area_name order by val";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }

}
